Kish_HP2: Employee Salaries Analysis

Author

John Kish

Import and Setup

Setup

# loading libraries
library(tidyverse)
library(here)
library(scales)
library(gt)

Import

# locating
here::i_am("analysis/Kish_HP2.qmd")
library(here)

# reading in data
# df was used instead of a longer name for the sake of my sanity in typing
df <- read.csv(here("data/Employee_Salaries.csv"))

Explanation of Data Variables

# Department
# Abbreviation/acronym for the department name. Example: ABS for the Alcohol Beverage Services department

# Department_Name
# the department name for all active, permanent employees of Montgomery County, MD (Maryland)


# Division
# The division for each department. That is, a single department can have multiple divisions and focuses.
# Example: ABS has the divisions that include those of ABS 85 Administrative Services
# and ABS 85 Beer Delivery Operations

# Gender
# Sex
# the Gender of M and F. Assuming that since this is public info, M is to be the legal sex of the individual M for Male and F for Female.

# Base_Salary
# precise base salaries (including down to the cent) for the individual, at the end of the calendar year.
# this is projected data. Should be accurate, but projection allows for greater flexibiliity.


# Overtime_Pay
# amount of overtime pay an employee/individual received for working beyond what they were required to do


# Longevity_Pay
# Longevity pay gives additional compensation for qualifying employees who have worked for a good deal of time. The longer time that is worked, the greater the longevity pay. Specifics about how long are dealt with in-house.


# Grade
# the standing an employee has within a company. The internal codes for all positions are not provided, but we can safely look at M1, M2, M3, etc. as those are clearly levels of management. There are thousands of grades.

# M4-Senior Manager (Not included in this particular data)
# M3-Middle Manager
# M2-Middle Manager
# M1-First Level Manager

Case Study: Employees of Montgomery County, MD paid in calendar year 2023

Workplace inequalities are a concern to many. We’ll be examining that topic — with the example of reliable and public data from Montgomery County, MD.

The employees in question are all active, permanent employees of Montgomery County, MD (Maryland).

Each employee works in a department, where each department can have multiple divisions.

In addition to one’s guaranteed base pay, an employee could earn overtime pay, longevity pay (when eligible for working at the company for a long time), both (additional compensation), either overtime or longevity pay, or neither.

These forms of pay (specifically, base pay and additional pay) sum to an employee’s total compensation.

Additional detail can be found above in “Explanation of Data Variables” tab.

Goal of Analysis

We will begin by exploring the relationship between base pay and total compensation across all employees.

Then, we will look to see how this relationship develops across gender, management, and department.

Outline of the questions we will be addressing:

Topic 1) How do base pay and additional pay relate to total compensation? is taking on a little more additional pay (at least if you can, like overtime) worth it?

Topic 2) Are there any disparities between the genders in light of the previous analysis?

Topic 3) Is management similar to overall jobs? Are Male and Female equally represented here? Is there evidence of a glass-ceiling, or it being harder for one sex to reach higher compensation due to real or imagined contraints?

Topic 4) How do the genders vary by department? Is it oversampling a gender so that we’re skewing our analyis?

Preliminary

First, let’s establish the data that we’ll be looking at

# Computing Total and Additional Compensation
df$Total_Compensation <- df$Base_Salary + df$Overtime_Pay + df$Longevity_Pay
df$Additional_Compensation <- df$Overtime_Pay + df$Longevity_Pay

# Reading Gender as Sex
df <- rename(df, Sex = "Gender")

# str(df)
# checking the datatypes for each variable
# df <- df |> mutate(across(where(is.double), as.integer))
# Double deemed more useful, can change to integer if further analysis needs integer type

# detecting NA Values
sum(is.na(df))

Additional compensation will be the combination of overtime and longevity pay.

Total compensation will be the combination of base pay and additional compensation.

In the data, there are 0 missing values. (Good news!)

Assuming that since this data comes what is available to verified by the public,

M is assumed to be the legal sex of the individual — M for Male and F for Female. That is, sex as legality rather than gender as identity seems to be what is implied. Occasionally, the term “gender” might be used here as well to refer to “sex”.

Topic 1: Total Compensation and Overtime

Preliminary Analysis

How many employees are there? How many of them get overtime, longevity, either, or both?

Preliminary Analysis

#each row is a unique employee, for each dataset


# Total Count & cresting Employee number column
Total_Employees <- nrow(df)
df <- mutate(df, Employee = row_number())

# Base Count & Percentage
Employees_Without_Base <- filter(df, Base_Salary == 0.00) 
Employees_With_Base <- anti_join(df, Employees_Without_Base)
Percent_With_Base <- (nrow(Employees_With_Base) / Total_Employees) * 100



# Over Count & Percentage
Employees_Without_Overtime <- filter(df, Overtime_Pay == 0.00)
Employees_With_Overtime <- anti_join(df, Employees_Without_Overtime)
Percent_Overtime <- (nrow(Employees_With_Overtime) / Total_Employees) * 100


# Long Count & Percentage
Employees_Without_Longevity <- filter(df, Longevity_Pay == 0.00)
Employees_With_Longevity <- anti_join(df, Employees_Without_Longevity)
Percent_Longevity <- (nrow(Employees_With_Longevity) / Total_Employees) * 100


# Either Over or Long Count & Percentage
Employees_Without_Over_Long <- filter(df, Overtime_Pay == 0.00 | Longevity_Pay == 0.00)
Employees_With_Over_Long <- anti_join(df, Employees_Without_Over_Long)
Percent_Over_Long <- (nrow(Employees_With_Over_Long) / Total_Employees) * 100

# Add Comp Count & Percentage
Employees_Without_Add_Comp <- filter(df, Additional_Compensation == 0.00)
Employees_With_Add_Comp <- anti_join(df, Employees_Without_Add_Comp)
Percent_Add_Comp <- (nrow(Employees_With_Add_Comp) / Total_Employees) * 100

There are 10291 employees total, where 0 employees have no base salary (100% have a base salary)

Approximately 16.3% have overtime pay, 27.6% have longevity pay, and 16.3% have one or the either, and 66.1% have both.

Diminishing Returns?

Plot Coefficients

Is overtime pay worth it? Does it have diminishing returns? If overtime pay (and other additional compensation) had diminishing returns, we would expect its slope to be less than 1. A slope of greater than 1 indicates that you get more and more total compensation for every bit more overtime and other additional pay you do. A higher slope indicates a greater degree of this scaling — which is desireable!

Preliminary Analysis Coefficients

Base_Total_lm <- lm(Total_Compensation ~ Base_Salary, 
           data = filter(df))

Over_Base_Total_lm <- lm(Total_Compensation ~ Base_Salary, 
           data = filter(Employees_With_Overtime))

Long_Base_Total_lm <- lm(Total_Compensation ~ Base_Salary, 
           data = filter(Employees_With_Longevity))

Over_Long_Base_Total_lm <- lm(Total_Compensation ~ Base_Salary, 
           data = filter(Employees_With_Over_Long))

Add_Comp_Base_Total_lm <- lm(Total_Compensation ~ Base_Salary, 
           data = filter(Employees_With_Add_Comp))

Based on the coefficients for the graphs, those who only get base pay have their total compensation scale up the least with those having overtime and longevity pay scaling up the most.

In particular, as is seen in the plots too, overtime pay scales slightly larger than longevity pay.

So, there does seem to be a non-zero difference between the slope of taking on overtime (and getting more pay) + total compensation and the slope of 1 (overtime pay is directly correlational with total compensation)

Plots

Preliminary Analysis Plots

#overall
p <- df |>
ggplot(mapping = aes(x = Base_Salary, y = Total_Compensation)) +
  geom_point(aes(color = "red")) +
  scale_x_continuous(labels = label_number(suffix = " K", scale = 1e-3)) +
  scale_y_continuous(labels = label_number(suffix = " K", scale = 1e-3)) +
    labs(x = "Base Salary", y = "Total Compensation", title = "Base Salary vs. Total Compensatory for All Employees") +
  geom_smooth(method = "lm") +
  theme(legend.position="none")+ 
  theme(plot.title = element_text(size=10))
p

The slope coefficient (intercept and then slope) is 3269.0284279, 1.070269.

Preliminary Analysis Plots

p2 <- Employees_With_Overtime |>
ggplot(mapping = aes(x = Base_Salary, y = Total_Compensation)) +
  geom_point(aes(color = "red")) +
  scale_x_continuous(labels = label_number(suffix = " K", scale = 1e-3)) +
  scale_y_continuous(labels = label_number(suffix = " K", scale = 1e-3)) +
    labs(x = "Base Salary", y = "Total Compensation", title = "Base Salary vs. Total Compensatory for All Those with Overtime Pay") +
  geom_smooth(method = "lm") +
    theme(legend.position="none")+ 
  theme(plot.title = element_text(size=10))
p2

The slope coefficient (intercept and then slope) is -1.1910607^{4}, 1.3303052.

Preliminary Analysis Plots

p3 <- Employees_With_Longevity |>
ggplot(mapping = aes(x = Base_Salary, y = Total_Compensation)) +
  geom_point(aes(color = "red")) +
  scale_x_continuous(labels = label_number(suffix = " K", scale = 1e-3)) +
  scale_y_continuous(labels = label_number(suffix = " K", scale = 1e-3)) +
    labs(x = "Base Salary", y = "Total Compensation", title = "Base Salary vs. Total Compensatory for All Those with Longevity Pay") +
  geom_smooth(method = "lm") +
    theme(legend.position="none")+ 
  theme(plot.title = element_text(size=10))
p3

The slope coefficient (intercept and then slope) is -1.1115563^{4}, 1.2845143.

Preliminary Analysis Plots

p4 <- Employees_With_Over_Long |>
ggplot(mapping = aes(x = Base_Salary, y = Total_Compensation)) +
  geom_point(aes(color = "red")) +
  scale_x_continuous(labels = label_number(suffix = " K", scale = 1e-3)) +
  scale_y_continuous(labels = label_number(suffix = " K", scale = 1e-3)) +
    labs(x = "Base Salary", y = "Total Compensation", title = "Base Salary vs. Total Compensatory for All Those with Overtime or Longevity Pay") +
  geom_smooth(method = "lm") +
    theme(legend.position="none")+ 
  theme(plot.title = element_text(size=10))
p4

The slope coefficient (intercept and then slope) is -3.2010085^{4}, 1.5664147.

Preliminary Analysis Plots

p5 <- Employees_With_Add_Comp |>
ggplot(mapping = aes(x = Base_Salary, y = Total_Compensation)) +
  geom_point(aes(color = "red")) +
  scale_x_continuous(labels = label_number(suffix = " K", scale = 1e-3)) +
  scale_y_continuous(labels = label_number(suffix = " K", scale = 1e-3)) +
    labs(x = "Base Salary", y = "Total Compensation", title = "Base Salary vs. Total Compensatory for All Those with Additional Compensation") +
  geom_smooth(method = "lm")+
    theme(legend.position="none")+ 
  theme(plot.title = element_text(size=10))
p5

The slope coefficient (intercept and then slope) is -4803.6504064, 1.2194241.

Diminishing Returns? Revisted: Percentages

How much of their total compensation is base + overtime, longevity, either, or both (In terms of percentage?)

Preliminary Analyis Percentage

# addiing to the original df a new column, using mutate, that is the percent of total
# compensation that is each of the relevant factors.

df <- group_by(df, Employee) |>
  mutate(Percent_Over = Overtime_Pay/Total_Compensation)
df$Percent_Over <- round(df$Percent_Over, 3) * 100

df <- group_by(df, Employee) |>
  mutate(Percent_Long = Longevity_Pay/Total_Compensation)
df$Percent_Long <- round(df$Percent_Long, 3) * 100

df <- group_by(df, Employee) |>
  mutate(Percent_Base = Base_Salary/Total_Compensation)
df$Percent_Base <- round(df$Percent_Base, 3) * 100

df <- group_by(df, Employee) |>
  mutate(Percent_Add_Comp = Additional_Compensation/Total_Compensation)
df$Percent_Add_Comp <- round(df$Percent_Add_Comp, 3) * 100

df <- group_by(df, Employee) |>
  mutate(Percent_Over_Long = Base_Salary/Total_Compensation)
df$Percent_Over_Long <- round(df$Percent_Over_Long, 3) * 100

Coefficients of the percentage things

Preliminary Analysis Percentage Coefficients

Percent_Base_Total_lm <- lm(Total_Compensation ~ Percent_Base, 
           data = filter(df))

Percent_Over_Total_lm <- lm(Total_Compensation ~ Percent_Over, 
           data = filter(df))

Percent_Long_Total_lm <- lm(Total_Compensation ~ Percent_Long, 
           data = filter(df))

Percent_Over_Long_Total_lm <- lm(Total_Compensation ~ Percent_Over_Long, 
           data = filter(df))

Percent_Add_Comp_Total_lm <- lm(Total_Compensation ~ Percent_Add_Comp, 
           data = filter(df))

In terms of these slope coefficients, longevity pay has the greatest scaling. Interestingly, the more percentage base pay you have, the less your total compensation! (negative slope!)